#!/usr/bin/env python
#-*- coding: UTF-8 -*-
import pymysql
import time
import datetime
import uuid
from requests.exceptions import RequestException
from bs4 import BeautifulSoup
from requests import get
import json
from email.mime.text import MIMEText
import smtplib
from email.header import Header
import random
import pandas as pd
from sqlalchemy import create_engine


class mysql_class:
    def __init__(self):
        self.host = 'localhost'
        self.user = 'root'
        self.passwd = 'hj0522'
        self.db = 'hej'
        self.codeList = ['000961']

    def ljdb(self):
        self.conn = pymysql.connect(host=self.host,
                                    user=self.user,
                                    passwd=self.passwd,
                                    db=self.db,
                                    port=3306,
                                    charset='utf8')
        # conn = pymysql.connect(ip, username, pwd, schema,port)
        self.conn.ping(True)  #使用mysql ping来检查连接,实现超时自动重新连接
        # print(getCurrentTime(), u"MySQL DB Connect Success:",user+'@'+host+':'+str(port)+'/'+db)
        self.cur = self.conn.cursor()

    def getCurrentTime(self):
        # 获取当前时间
        return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

    # 更新数据
    def updateData(self, table, my_dict, tid):
        cv = ''
        for (k, v) in my_dict.items():
            if cv == '':
                cv += k + ' = "' + v + '"'
            else:
                cv += ', ' + k + ' = "' + v + '"'

        sql = "update %s set %s where id = %s" % (table, cv, '"' + tid + '"')
        # print(sql)
        self.cur.execute(sql)
        self.conn.commit()

    # 数据处理
    def selectData(self, code):
        # engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')
        engine = create_engine("mysql+pymysql://root:" + self.passwd +
                               "@localhost:3306/" + self.db)
        sql = "select id,riqi as date,ifnull(jj_name,'') as name,dwjz as unitNet from jj_history where jj_code = '" + code + "' ORDER BY riqi"
        df = pd.read_sql_query(sql, engine)
        # print(df)

        # print(df.info())

        # CCI指标
        # 获取5日均线
        df['ma5'] = df['unitNet'].rolling(5).mean()

        # 6、计算布林线指标
        # 中轨（20日均线）
        df['g1'] = df['unitNet'].rolling(20).mean()
        # 数据1     (净值-中轨)的平方
        # df['s1'] = (df['unitNet'] - df['g1']) * (df['unitNet'] - df['g1'])
        df['s1'] = (df['unitNet'] - df['g1'])**2
        # 数据2     数据1的20日均线
        df['s2'] = df['s1'].rolling(20).mean()
        # 数据3     数据2的平方根
        df['s3'] = df['s2']**0.5
        # 上轨      中轨+2*数据3
        df['g2'] = df['g1'] + 2 * df['s3']
        # 下轨      中轨-2*数据3
        df['g3'] = df['g1'] - 2 * df['s3']
        # =(2*[@单位净值2]-[@上轨]-[@下轨])/[@单位净值2]
        # 量能柱
        df['lnz'] = (2 * df['unitNet'] - df['g2'] - df['g3']) / df['unitNet']
        # [@中轨]+([@上轨]-[@中轨])*0.309
        # 高位止盈线
        df['gwvyx'] = df['g1'] + (df['g2'] - df['g1']) * 0.309
        # =[@中轨]-([@中轨]-[@下轨])*0.309
        # 低位加仓线
        df['dwjcx'] = df['g1'] - (df['g1'] - df['g3']) * 0.309



        # 去除缺失行
        df = df.dropna()
        for i in range(1, len(df)):
            # print(df[i:i+1])

            # 当天
            d = df[i:i + 1]
            # d = df.iloc[i]
            # 昨天
            d1 = df[i - 1:i]
            # d1 = df.iloc[i-1]

            # print(d)
            # print(d1)

            # IF(AND(E4>G4,[@单位净值2]<[@上轨]),"清仓"
            if float(d1['unitNet']) > float(d1['g2']) and float(
                    d['unitNet']) < float(d['g2']):
                # print(d.index)
                # d['ypck'] = "清仓"
                df.loc[d.index, 'ypck'] = "清仓"
                # print(d['unitNet'])
            # IF(AND(E4>M4,E4<G4,[@单位净值2]<[@高位止盈线]),"止盈赎回"
            elif float(d1['unitNet']) > float(d1['gwvyx']) and float(
                    d1['unitNet']) < float(d1['g2']) and float(
                        d['unitNet']) < float(d['gwvyx']):
                df.loc[d.index, 'ypck'] = "止盈赎回"
            # IF([@单位净值2]>[@低位加仓线],"观望期",
            elif float(d['unitNet']) > float(d['dwjcx']):
                df.loc[d.index, 'ypck'] = "观望期"
            # IF([@单位净值2]>[@下轨],"可加仓","探底加仓")
            elif float(d['unitNet']) > float(d['g3']):
                df.loc[d.index, 'ypck'] = "可加仓"
            else:
                df.loc[d.index, 'ypck'] = "探底加仓"

            if i > 30:

                # 计算CCI值
                # =(B2-SUM(H2:H31)/30)/AVEDEV(H2:H31)/0.015
                # 平均值：mean()        平均绝对偏差：mad()         苦恼mad()不能和rolling()一同使用
                ddf = df[i - 29:i + 1]
                df.loc[d.index,
                       'cci'] = (d['unitNet'] -
                                 ddf['ma5'].mean()) / ddf['ma5'].mad() / 0.015

        # print(df)
        # print(df.loc[38, 'id'])
        # print(df[i:i + 1]['name'])


        # print(df.loc[1, 'g1'])

        # print(df)
        # d = df.loc[38]
        # print(d)
        # print(d.index)
        print(d.key)
        


        # # 去除缺失行
        # df = df.dropna()
        # for i in range(1, len(df)):
        #     try:
        #         print(df.loc[i, 'id'])
        #     except RequestException:
        #         print("RequestException")
        #         return None


            # print(df.loc[1,["id"]])
            # print(df.loc[1, 'id'])

            # 当天
            # d = df[i]
            # d = df[i:i + 1]
            

            # result = {}
            # result['ypck'] = str(d['ypck'])
            # # print(str(d['id']))
            # # self.updateData('jj_history', result, d['id'])

        # print(df)

        # print(df)
        # print(df.iloc[-1]['name'])
        # print(df.iloc[-1]['ypck'])
        # return df.iloc[-1]['name'] + '   ' + df.iloc[-1]['ypck'] + '   ' + str(round(df.iloc[-1]['cci'],2)) + '\n'
        # return code + '   ' + df.iloc[-1]['ypck'] + '   ' + str(round(df.iloc[-1]['cci'],2)) + '\n'


if __name__ == "__main__":

    starttime = datetime.datetime.now()
    print(starttime)

    mysql_class = mysql_class()
    mysql_class.ljdb()  # 连接MySQL
    mysql_class.codeList = [
        '001548',
        '000961',
        '000962',  # 50/300/500
        '000071',  # 恒生
        '001508',
        '000727',
        '000751',  # 动力灵活、健康灵活、嘉实新兴产业
        '161721',  # 招商地产      （不打算持有了）
        '001631',  # 食品饮料
        '161725',  # 白酒
        '320007',  # 诺安
        '001618',  # 电子
        '004070',  # 证券
        '001595',  # 银行
        '166002',  # 中欧蓝筹
        '163402',  # 兴全趋势
        '519697',  # 交银优势
        '005224'
    ]  # 基建工程

    # mysql_class.codeList = ['000961']       # 基建工程

    mysql_class.selectData('001548')  # 数据处理

    endtime = datetime.datetime.now()
    print(endtime)

    print('\n数据处理成功!所用时间为：' +  str((endtime - starttime).seconds))
